package org.petstore.persistence.impl;

import org.petstore.domain.CartItem;
import org.petstore.domain.Item;
import org.petstore.persistence.CartDAO;
import org.petstore.persistence.DBUtil;
import org.petstore.service.CatalogService;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class CartDAOImpl implements CartDAO {
    private static final String INSERT_ITEM_BY_USERID_AND_ITEMID = "insert into cart(userId,itemId,inStock,quantity,totalCost) values(?,?,?,?,?)";
    private static final String SELECT_ITEM_BY_USERID_AND_ITEMID = "select * from cart where userId = ? and itemId = ?";
    private static final String SELECT_ITEM_BY_USERID = "select * from cart where userId = ?";
    private static final String UPDATE_ITEM_BY_USERID_AND_ITEMID = "update cart set quantity = ?, totalCost = ? where userId = ? and itemId = ?";
    private final static String REMOVE_ITEM_BY_USERID_AND_ITEMID = "delete from cart where userId = ? and itemId = ?";
    private final static String UPDATE_ITEM_BY_USERID_AND_ITEMID_AND_PAY = "update cart set pay = ? where userId = ? and itemId = ?";
    @Override
    public int updateItemByUsernameAndItemId(String userId, String itemId, int quantity, BigDecimal totalCost) {
        int result = 0;
        try{
            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(UPDATE_ITEM_BY_USERID_AND_ITEMID);
            preparedStatement.setInt(1,quantity);
            preparedStatement.setBigDecimal(2,totalCost);
            preparedStatement.setString(3,userId);
            preparedStatement.setString(4,itemId);
            result = preparedStatement.executeUpdate();
            DBUtil.closePreparedStatement(preparedStatement);
            DBUtil.closeConnection(connection);
        }
        catch (Exception e){
            e.printStackTrace();
        }
        return result;
    }

    @Override
    public CartItem selectItemByUsernameAndItemId(String userId, String itemId) {
        CartItem result = null;
        try {
            Connection connection = DBUtil.getConnection();
            PreparedStatement pStatement = connection.prepareStatement(SELECT_ITEM_BY_USERID_AND_ITEMID);
            pStatement.setString(1,userId);
            pStatement.setString(2,itemId);
            ResultSet resultSet = pStatement.executeQuery();
            if(resultSet.next()){
                result = new CartItem();
                CatalogService catalogService = new CatalogService();
                boolean isInStock = resultSet.getBoolean(3);
                int quantity = resultSet.getInt(4);
                BigDecimal totalCost = resultSet.getBigDecimal(5);
                boolean pay = resultSet.getBoolean(6);
                result.setItem(catalogService.getItem(itemId));
                result.setInStock(isInStock);
                result.setQuantity(quantity);
                result.setTotal(totalCost);
                result.setPay(pay);
            }
            DBUtil.closeResultSet(resultSet);
            DBUtil.closePreparedStatement(pStatement);
            DBUtil.closeConnection(connection);
        }catch (Exception e){
            e.printStackTrace();
        }
        return result;
    }

    @Override
    public void insertItemByUsernameAndItemId(String userId, String itemId,boolean inStock, int quantity, BigDecimal totalCost) {
        try {
            Connection connection = DBUtil.getConnection();
            PreparedStatement pStatement = connection.prepareStatement(INSERT_ITEM_BY_USERID_AND_ITEMID);
            pStatement.setString(1,userId);
            pStatement.setString(2,itemId);
            pStatement.setBoolean(3,inStock);
            pStatement.setInt(4,quantity);
            pStatement.setBigDecimal(5,totalCost);
            pStatement.executeUpdate();
            DBUtil.closePreparedStatement(pStatement);
            DBUtil.closeConnection(connection);
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    @Override
    public List<CartItem> selectItemByUsername(String userId) {
        List<CartItem> cartList = null;
        try{
            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(SELECT_ITEM_BY_USERID);
            preparedStatement.setString(1,userId);
            ResultSet resultSet = preparedStatement.executeQuery();
            cartList = new ArrayList<CartItem>();
            while (resultSet.next()) {
                CatalogService catalogService = new CatalogService();
                CartItem result = new CartItem();
                String itemId = resultSet.getString(2);
                boolean isInStock = resultSet.getBoolean(3);
                int quantity = resultSet.getInt(4);
                BigDecimal totalCost = resultSet.getBigDecimal(5);
                boolean pay = resultSet.getBoolean(6);
                if(!pay) {
                    result.setItem(catalogService.getItem(itemId));
                    result.setInStock(isInStock);
                    result.setQuantity(quantity);
                    result.setTotal(totalCost);
                    cartList.add(result);
                }
            }
            DBUtil.closePreparedStatement(preparedStatement);
            DBUtil.closeConnection(connection);
        }
        catch (Exception e){
            e.printStackTrace();
        }
        return cartList;
    }

    @Override
    public void removeItemByUsernameAndItemId(String username, String itemId) {
        try {
            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(REMOVE_ITEM_BY_USERID_AND_ITEMID);
            preparedStatement.setString(1,username);
            preparedStatement.setString(2,itemId);
            int result = preparedStatement.executeUpdate();
            DBUtil.closePreparedStatement(preparedStatement);
            DBUtil.closeConnection(connection);
        }
        catch (Exception e){
            e.printStackTrace();
        }
    }

    @Override
    public void updateItemByItemIdAndPay(String username, String itemId, boolean pay) {
        try{
            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(UPDATE_ITEM_BY_USERID_AND_ITEMID_AND_PAY);
            preparedStatement.setBoolean(1,pay);
            preparedStatement.setString(2,username);
            preparedStatement.setString(3,itemId);
            int result = preparedStatement.executeUpdate();
            DBUtil.closePreparedStatement(preparedStatement);
            DBUtil.closeConnection(connection);
        }
        catch (Exception e){
            e.printStackTrace();
        }
    }
}
